# -*- coding:utf-8 -*-
"""
功能描述：    本模块按照测试中心案例标准化要求，把不合规案例处理为合格案例。
术语：     given - 前置条件；when - 执行步骤；then - 预期结果
作者：    黄小明
版本：    V1.2
日志：    Created 2018-6-29
          modified 2018-07-05
"""
import sys
import os
import string
import logging
import traceback
import re

from importlib import reload
from ExcelLibrary import ExcelLibrary
from functools import reduce

"""
    Documentation：
    /**********************************************     文件可配置项  ************************************************/
    1.可以设置案例输入目录和输出目录，默认输入目录 InputDir=当前目录，默认输出目录 OutputDir=当前目录/Temp目录
    2.可以配置要转换的列 TRANS_COLUMN =  D：描述  C：前置条件 S：步骤  R：预期结果  权重 1 2 4 8 根据权重就可以判断要格式化哪些项
    3.可以对每个Excel文件配置步骤描述-预期结果对应类型 ASSOCIATED_MODE = P：成对   L：最后一次才有预期结果 D：由分号来判断检查点
    4.描述格式化方式 DESC_FORMATTER_MODE = S:简单格式化 C:复杂格式化
    5.可以设置要转换的sheet名称、或者根据格式来判断
    6.可以设置、增加、删除预期结果关键字



    /***********************************************    V1.1改进项   ************************************************/
    1.由脚本变为配置类，增加可复用性和可移植性                            -----------完成
    2.使用Python3重新编写，减少编码问题                                  -----------完成
    3.增加Log日志，便于调试                                             -----------完成
    4.给转换后的Excel文件名+Tag，这样可以同时打开进行比较修改             ------------完成
    5.界面化，使用界面进行配置、管理                                     ----------待完成
    6.log打印没有stack_trace问题                                        ------------完成
    7.在转换过程中保存正在转换的文件名+sheet名+行号出错时进行打印          ------------完成
    8.可选格式化项功能                                                  -------------完成
    9.步骤-预期结果设置功能                                             -------------完成
    10.设置Excel输入目录、Excel输出目录功能                              -------------完成
    11.修复自测出来的若干个Bug                                          -------------完成
    12.设置转换后的Excel自动换行                                        -------------待完成
    13.输出文件后缀改成小写

    /***********************************************  V1.2需求     ************************************************/
    1.后缀统一改小写
    2.增加UI界面，可以选择Excel输入目录
    3.增加起始列、结束列选项、只对设定的列进行转换
    4.步骤描述列以  步骤N开头增加处理、考虑只需增加"#"的情况
    5.增加对预期结果从案例标准Version1 转换到Version2


    /************************************************   整体结构 and API列表  *****************************************/

                ------------   案例转换执行入口 ----------------
                1.{void} execute {void}


                ------------   业务处理函数  ------------------
                1.{formatted_case_desc}  cell_description_given_formatter {case_desc}
                    DESC：用来对案例描述信息和前置条件格式化
                    PARAM：case_desc {String} 案例描述信息或前置条件信息，从单元格读取
                    OUTPUT：formatted_case_desc {String} 格式话后的字符串

                2.{formatted_when_desc，step_num,expected_check_num,check_list}  cell_when_formatter {when_desc}
                    DESC：步骤信息格式化
                    PARAM：when_desc ｛String｝步骤描述信息，从单元格读取
                    OUTPUT：formatted_step_desc {String} 格式化后的步骤描述
                            step_num {int} 步骤数
                            expected_check_num ｛int｝ 预期检查点个数也就是预期结果数
                            check_list {list} 检查点列表，根据ASSOCIATED_MODE有所不同

                3 ｛formatted_then_desc｝cell_then_formatter {then_desc,step_num,expected_check_num,check_list}
                    DESC：预期结果格式化
                    PARAM：then_desc ｛String｝ 预期结果描述信息
                           step_num：步骤数
                           expected_check_num：期望的预期结果步数
                           check_list：预期结果对应步骤序号
"""


class BaseCaseFormatter:
    def __init__(self):
        self.logger = BaseCaseFormatter.init_logger()
        self.sequence_rule = [
            ["1、", "1："], ["1.", "1："], ["1 ", "1："],
            ["2、", "2："], ["2.", "2："], ["2 ", "2："],
            ["3、", "3："], ["3.", "3："], ["3 ", "3："],
            ["4、", "4："], ["4.", "4："], ["4 ", "4："],
            ["5、", "5："], ["5.", "5："], ["5 ", "5："],
            ["6、", "6："], ["6.", "6："], ["6 ", "6："],
            ["7、", "7："], ["7.", "7："], ["7 ", "7："],
            ["8、", "8："], ["8.", "8："], ["8 ", "8："],
            ["9、", "9："], ["9.", "9："], ["9 ", "9："],
            ["10、", "10："], ["10.", "10："], ["10 ", "10："]
        ]
        self.accounting_keyword = ['贷', 'Dr', 'Cr', 'DR', '记账', '账务', '会计', '核算', '入账']  # 【账务分录】
        self.ui_keyword = ['布局', '跳转', '回显', '下拉', '只读', '可选', '必输项', '长度', '提示', '红字', '录入',
                           '选中', '弹出', '输入', '勾选', '正确打开', '正确进入', '成功打开', '成功进入', '可输',
                           '置灰', '可点']  # 【UI检查】
        self.logic_db_keyword = ['数据库', '字段', '文件', '更新', '保存', '插入']  # 【业务及数据文件检查】
        self.case_upload = True
        self.separator = "#"
        self.TRANS_COLUMN = 15  # D+C+S+R,默认全部转换
        self.DESC_FORMATTER_MODE = 'S'
        self.xls_list = []  # 暂不使用                [[filename1, ASSOCIATED_MODE], [filename2,ASSOCIATED_MODE], ...]
        self.ASSOCIATED_MODE = 'P'  # 默认预期结果与步骤成对
        self.use_default_dir = True
        self.input_dir = os.getcwd().replace("\\", "/")
        self.output_dir = os.getcwd().replace("\\", "/") + "/" + "Temp"
        self.property_dict = {}
        self.current_file = ""
        self.current_row_no = 0

    # ##############    业务函数  ###############################
    def cell_description_given_formatter(self, description_str):
        if description_str is None or description_str.strip() == "":
            return ""
        description_str = description_str.strip()
        description_str = description_str.replace(";", "；")
        description_str = description_str.replace(",", "，")
        if description_str[0].isdigit():
            for each_rule in self.sequence_rule:
                index = description_str.find(each_rule[0])
                if index == 0:
                    description_str = description_str.replace(each_rule[0], each_rule[1])
                if index > 0:
                    prefix = description_str[:index]
                    suffix = description_str[index:]
                    prefix = prefix.strip()
                    while "，；。#".find(prefix[-1]) != -1:
                        prefix = prefix[:-1]
                    prefix += "；\n"
                    description_str = prefix + suffix.replace(each_rule[0], each_rule[1])
        else:
            description_str = description_str.strip()
            description_str = "1：" + description_str
        while description_str.find("\n\n") != -1:
            description_str = description_str.replace("\n\n", "\n")
        while description_str.find("\r\n") != -1:
            description_str = description_str.replace("\r\n", "\n")
        description_str.strip()
        while "，；。".find(description_str[-1]) != -1:
            description_str = description_str[:-1]
        description_str += "。"
        print(description_str)
        return description_str

    def cell_when_formatter(self, description_str):
        """
        规范化 执行步骤 ：根据序号进行拆分，一个序号就是一个步骤，每个步骤最后带分号的识别为带检查点
        :param description_str:
        :return:result_str 格式化的操作步骤,
         step_num 操作步骤个数为,
         check_count, 包含结果检查的步骤个数为
         check_step_position ,结果检查所在的步骤为以下几步，格式为数组LIST
        """
        if description_str is None or description_str.strip() == "":
            raise Exception("执行步骤为空，请检查Excel表格")
        description_str = description_str.strip()
        row_list = self.divide_into_rows(description_str)  # EXCEL单元格里的行数据
        print(len(row_list))
        step_num = len(row_list)  # 测试步骤编号
        expected_check_count = 1  # 有结果检查的步骤个数
        check_step_position = list()  # 存放
        result_str = ''  # 格式化处理后的测试执行步骤
        separator = "；" + self.separator + "\n"
        if len(row_list) == 1:
            check_step_position.append(1)
            result_str = row_list[0]
            result_str = "步骤" + result_str
            while "，。；#".find(result_str[-1]) != -1:
                result_str = result_str[:-1]
            result_str += "。"
        elif len(row_list) > 1:
            if self.ASSOCIATED_MODE == "P" or self.ASSOCIATED_MODE == "L":  # 预期结果与步骤序号匹配
                if self.ASSOCIATED_MODE == "P":
                    check_step_position = range(1, step_num + 1)
                    expected_check_count = step_num
                else:
                    check_step_position.append(step_num)
                    expected_check_count = 1
                for each_row in row_list:
                    while "，。；#".find(each_row[-1]) != -1:
                        each_row = each_row[:-1]
                    each_row = "步骤" + each_row + separator
                    result_str += each_row
                result_str = result_str[:-len(separator)] + "。"
            elif self.ASSOCIATED_MODE == "D":
                index = 1
                for each_row in row_list:
                    if index == step_num or each_row.endswith("；"):
                        check_step_position.append(index)
                        expected_check_count += 1
                    index += 1
                    while "，。；#".find(each_row[-1]) != -1:
                        each_row = each_row[:-1]
                    each_row = "步骤" + each_row + separator
                    result_str += each_row
                result_str = result_str[:-len(separator)] + "。"
        return result_str, step_num, expected_check_count, check_step_position

    def cell_then_formatter(self, description_str, step_num, expected_check_count, check_step_position):
        """
        获取执行步骤中理论上存在检查的步骤数
        :param description_str: 预期结果excel表格的描述
        :param  step_num 操作步骤个数为,
        :param  check_count, 包含结果检查的步骤个数为
         :param check_step_position ,结果检查所在的步骤为以下几步，格式为数组LIST
        :return: 格式化的预期结果  abnormal_flag=1 表示转换前的案例存在异常
        """
        description_str = description_str.strip()
        if description_str.startswith("步骤") and description_str[2].isdigit() and description_str[3] == "：":  # TODO
            self.process_version1_result(description_str)
            return "测试测试"
        abnormal_flag = 0
        step_check_dict = dict()
        if step_num <= 0:
            print("ERROR: 操作步骤个数小于等于0")
            abnormal_flag = 1
        for i in range(1, step_num + 1):  # 把每一个步骤对应的检查描述初始化
            step_check_dict[i] = "步骤" + str(i) + "：无\n"
        checks_list = self.divide_into_rows(description_str)  # 以;\n分隔的预期结果文字块LIST
        check_count = len(checks_list)  # 预期结果中存在结果检查的步骤数目
        ##  对每行数据数据进行初始化处理：去除多余的空白符及标点符号  ##
        temp_check_list = []
        for check in checks_list:
            check = check.strip()
            while "，；。#".find(check[-1]) != -1:
                check = check[:-1]
            check += "；\n"
            temp_check_list.append(check)
        temp_check_list[-1] = temp_check_list[-1][:-2] + "。"
        checks_list = temp_check_list

        if self.ASSOCIATED_MODE == "P":
            if check_count != expected_check_count:  # 如果预期结果中检查点个数 与 操作步骤中检查个数不一致，则报错，把异常标志位置为1
                abnormal_flag = 1
                expected_last_index = self.parse_index_from_string(checks_list[-1])
                if expected_last_index != step_num:  # 个数不一致当作最后一步预期结果处理
                    check_str = ""
                    for check in checks_list:
                        check_str += check
                    check_step_position = [step_num]
                    checks_list = [check_str]
                else:  # 个数一致时
                    check_step_position = []
                    for check in checks_list:
                        index = self.parse_index_from_string(check)
                        check_step_position.append(index)
            else:
                pass
        elif self.ASSOCIATED_MODE == "D":
            if check_count != expected_check_count:  # 检查点个数与预期结果个数不符时，当作最后一步的预期结果
                abnormal_flag = 1
                check_str = ""
                for check in checks_list:
                    check_str += check
                check_step_position = [step_num]
                checks_list = [check_str]
            else:
                pass
        elif self.ASSOCIATED_MODE == "L":
            check_str = ""
            for check in checks_list:
                check_str += check + "\n"
            check_step_position = [step_num]
            checks_list = [check_str]
        index_for_checks_list = 0
        for step_seq in check_step_position:
            step_check_dict[step_seq] = "步骤" + str(step_seq) + "：\n" + self.check_desc_format(
                checks_list[index_for_checks_list])
            index_for_checks_list += 1
        result_str = ''
        for i in range(1, step_num + 1):  # 把每一个步骤对应的检查描述初始化
            result_str = result_str + step_check_dict[i].strip() + self.separator + "\n"
        result_str = result_str.rstrip(self.separator + "\n")
        return result_str, abnormal_flag

    def check_desc_format(self, check_str):
        """
        把每一个步骤预期结果格式化
        :param check_str:
        :return:
        """
        checks_dict = self.divide_into_parts(check_str)  # 预期结果的字典集合
        count = len(checks_dict)
        account_index = 1
        ui_index = 1
        logic_db_index = 1
        result_str = ''
        for i in range(count):
            single_check = checks_dict[i].strip()
            if self.classify_check_type(single_check) == 0:  # 账务
                if account_index == 1:
                    result_str = result_str + "【账务分录】\n" + str(account_index) + single_check[1:] + "\n"
                else:
                    result_str = result_str + str(account_index) + single_check[1:] + "\n"
                account_index += 1
            elif self.classify_check_type(single_check) == 1:  # UI
                if ui_index == 1:
                    result_str = result_str + "【UI检查】\n" + str(ui_index) + single_check[1:-1] + "。\n"
                else:
                    result_str = result_str[:-2] + "；\n"
                    result_str = result_str + str(ui_index) + single_check[1:-1] + "。\n"
                ui_index += 1
            else:  # 业务及数据文件检查
                if logic_db_index == 1:
                    result_str = result_str + "【业务及数据文件检查】\n" + str(logic_db_index) + single_check[1:-1] + "。\n"
                else:
                    result_str = result_str[:-2] + "；\n"
                    result_str = result_str + str(logic_db_index) + single_check[1:-1] + "。\n"
                logic_db_index += 1
        return result_str

    def classify_check_type(self, description_str):
        """
        根据检查点描述，判断合格检查点的类型：账务分录、业务及数据文件检查、UI检查
        check_type_rule_accounting = ['借','贷','Dr','Cr','DR','CR','账务','会计','核算','187501']  # 【账务分录】
        check_type_rule_ui = ['界面', '必输项', '长度', '页面', '提示', '空格']  # 【UI检查】
        check_type_rule_logic_db = ['数据库', '字段', '文件', '更新', '保存','插入' ]  # 【业务及数据文件检查】
        :param description_str:
        :return: 0 - 账务， 1 - UI ， 2， 业务及数据文件检查
        """
        for each_rule in self.ui_keyword:
            if description_str.find(each_rule) >= 0:  # 匹配到ＵＩ关键字
                return 1
        for each_rule in self.accounting_keyword:
            if description_str.find(each_rule) >= 0:  # 匹配到账务关键字
                return 0
        return 2

    def test_case_formatter(self, case_file, associated_mode="P", out_dir_mode="U"):
        """
        处理逻辑：
        循环处理每一行记录
        统计信息：case总个数、成功处理数、失败列表
        :param case_file:
        :return:
        """
        # 读案例EXCEL
        self.ASSOCIATED_MODE = associated_mode
        slash_index = case_file.rfind("/")
        path = case_file[:slash_index]
        file_name_with_suffix = case_file[slash_index+1:]
        dot_index = file_name_with_suffix.rfind(".")
        file_name = file_name_with_suffix[:dot_index]
        suffix = file_name_with_suffix[dot_index+1:]
        if out_dir_mode == "S":            # 输出Excel文件与输入Excel同一目录
            xls_dir = path
        elif out_dir_mode == "U":             # 输出Excel文件为指定目录。默认为：程序当前目录/TEMP
            xls_dir = self.output_dir
        if not os.path.exists(xls_dir) :
            os.mkdir(xls_dir)
        new_file_name = xls_dir + "/" + file_name+"_NEW"+"."+suffix.lower()
        el_case = ExcelLibrary()
        el_case.open_excel(case_file)
        sheet_name = "案例导入表"
        self.current_file = case_file
        row_count = el_case.get_row_count(sheet_name)  # 案例行数
        #有效案例行数
        valid_bit_map = [0 for x in range(row_count)]
        for i in range(3, row_count):
            if str(el_case.read_cell_data_by_coordinates(sheet_name, 0, i)).strip() != '':
                valid_bit_map[i] = 1
        valid_row_count = reduce(lambda x, y: x + y, valid_bit_map)
        print('sheet name:', sheet_name, '行数：', valid_row_count)
        # 建立案例EXCEL字典 key为案例路径 value为label
        for i in range(3, row_count):
            try:
                self.current_row_no = i
                if valid_bit_map[i] == 0:
                    continue
                if valid_row_count == 0:
                    break
                valid_row_count -= 1
                case_desc = str(el_case.read_cell_data_by_coordinates(sheet_name, 3, i))
                given_desc = str(el_case.read_cell_data_by_coordinates(sheet_name, 4, i))
                when_desc = str(el_case.read_cell_data_by_coordinates(sheet_name, 5, i))
                then_desc = str(el_case.read_cell_data_by_coordinates(sheet_name, 6, i))
                case_desc = self.cell_description_given_formatter(case_desc)
                given_desc = self.cell_description_given_formatter(given_desc)
                when_desc_tuple = self.cell_when_formatter(when_desc)
                when_desc = when_desc_tuple[0]
                then_desc_tuple = self.cell_then_formatter(then_desc, when_desc_tuple[1], when_desc_tuple[2],
                                                           when_desc_tuple[3])
                then_desc = then_desc_tuple[0]
                abnormal_flag = then_desc_tuple[1]  # 异常标志
                print('当前处理第N行：--------------')
                print(i + 1)
                print('案例异常提醒---------------')
                print(abnormal_flag)
                if self.TRANS_COLUMN & 1 == 1:
                    el_case.put_string_to_cell(sheet_name, 3, i, case_desc)
                    print('格式化案例描述---------------')
                    print(case_desc)
                    self.logger.info('格式化案例描述---------------')
                    self.logger.info(case_desc)
                if self.TRANS_COLUMN & 2 == 2:
                    el_case.put_string_to_cell(sheet_name, 4, i, given_desc)
                    print('格式化前置条件---------------')
                    print(given_desc)
                    self.logger.info('格式化前置条件---------------')
                    self.logger.info(given_desc)
                if self.TRANS_COLUMN & 4 == 4:
                    el_case.put_string_to_cell(sheet_name, 5, i, when_desc)
                    print('格式化步骤描述---------------')
                    print(when_desc)
                    self.logger.info('格式化步骤描述---------------')
                    self.logger.info(when_desc)
                if self.TRANS_COLUMN & 8 == 8:
                    el_case.put_string_to_cell(sheet_name, 6, i, then_desc)
                    print('格式化预期结果---------------')
                    print(then_desc)
                    self.logger.info('格式化预期结果---------------')
                    self.logger.info(then_desc)
                print('数据格式化完毕，准备写入EXCEL')
                print("End process...")
                el_case.save_excel(new_file_name)
            except PermissionError:
                exception_info = traceback.format_exc()
                print(exception_info)
                self.logger.error(exception_info)
                break
            except Exception:
                format_row_info_str = "Excel名称：" + self.current_file + "Sheet名称：案例导入表" \
                                      + "第" + str(self.current_row_no) + "行"
                self.logger.error(format_row_info_str + "格式化异常")
                exception_info = traceback.format_exc()
                self.logger.error(exception_info)


    #######  辅助函数   ##########
    def divide_into_rows(self, description_str):
        """
        把EXCEL一个单元格里的字符串形式的数据，拆分成一行行的数据，返回一个数组，每个数组元素为excel 单元格中的一行数据，每个元素最后不包括换行符
        :param description_str:
        :return:
        """
        if description_str is None or description_str.strip() == "":
            return list()
        description_str = description_str.strip()
        row_list = []
        description_str = description_str.replace(";", "；")
        description_str = description_str.replace(",", "，")
        description_str = description_str.replace(":", "：")
        if description_str.startswith("步骤") and description_str[2].isdigit():
            pass
        elif description_str[0].isdigit():
            if description_str[1] == "：":
                row_list = description_str.split("\n")
                for i in range(len(row_list)):
                    row_list[i] = row_list[i].strip()
                return row_list
            for each_rule in self.sequence_rule:
                index = description_str.find(each_rule[0])
                if index == 0:
                    description_str = description_str.replace(each_rule[0], each_rule[1])
                if index > 0:
                    row = description_str[:index]
                    row = row.strip()
                    row_list.append(row)
                    description_str = description_str[index:]
                    description_str = description_str.replace(each_rule[0], each_rule[1])
            description_str = description_str.strip()
            row_list.append(description_str)
        else:
            description_str = "1：" + description_str
            row_list.append(description_str)
        return row_list

    def divide_into_parts(self, description_str):
        if description_str is None or description_str.strip() == "":
            return list()
        description_str = description_str.strip()
        row_list = []
        for each_rule in self.sequence_rule:
            index = description_str.find(each_rule[1])
            if index > 0:
                row = description_str[:index]
                row = row.strip()
                row_list.append(row)
                description_str = description_str[index:]
        description_str = description_str.strip()
        row_list.append(description_str)
        return row_list

    def process_version1_result(self, desc_str):  # TODO
        result_list = []
        result_str = ""
        desc_str = desc_str.replace(",","，")
        desc_str = desc_str.replace(":","：")
        pattern = "步骤\d+[：、.]"
        position_list = [m.start() for m in re.finditer(pattern, desc_str)]
        print(position_list)
        for i in range(len(position_list)):
            if i != len(position_list) - 1:
                step_result = desc_str[position_list[i]:position_list[i+1]]
                print("step_result："+step_result)
                result = self.process_result_step(step_result)
                result_list.append(result)
        step_result = desc_str[position_list[-1]:]
        result = self.process_result_step(step_result)
        result_list.append(result)
        for result in result_list:
            suffix = ",;.。，；"+self.separator
            result = result.strip()
            while suffix.find(result[-1]) != -1:
                result = result[:-1]
            result += "。"+self.separator+"\n"
            result_str += result
        result_str = result_str.strip()
        result_str = result_str.rstrip(self.separator)
        return result_str

    @staticmethod
    def process_result_step(step_result):  # TODO
        logic_db_list = []
        accounting_list = []
        ui_list = []
        step_result = step_result.strip()
        pattern = "步骤\d+[、.：]"
        matcher = re.match(pattern, step_result)
        end_pos = matcher.end()
        print("Start:"+step_result[:end_pos])
        print("Next:"+step_result[end_pos:])

        new_line_pos = end_pos
        ######处理  步骤X:XXXXXXXXXX  ######
        if step_result[end_pos] != "\n":
            to_classify = ""
            new_line_pos = step_result.find("\n")
            if new_line_pos != -1:
                to_classify = step_result[end_pos:new_line_pos]
                print("Classify:"+to_classify)
            else:
                to_classify = step_result[end_pos:]
                print("Classify:"+to_classify)
            if self.classify_check_type(to_classify) == 1:
                ui_list.append(to_classify)
        ##### 处理其余部分   ###########
        retain = step_result[new_line_pos:]
        retain = retain.strip()   # TODO
        if retain[0] == "【":
            right_sign_pos = retain.find("】")
            if retain[1:right_sign_pos] == "UI检查":
                pass

    @staticmethod
    def parse_index_from_string(desc):
        desc = desc.strip()
        num_str = ""
        while desc[0].isdigit():
            num_str += desc[0]
            desc = desc[1:]
        try:
            num = int(num_str)
            return num
        except Exception:
            return -1

    @staticmethod
    def init_logger():
        # 获取logger实例，如果参数为空则返回root logger
        logger = logging.getLogger("AppName")
        # 指定logger输出格式
        formatter = logging.Formatter('%(asctime)s %(levelname)-8s:\n %(message)s')
        # 文件日志
        file_handler = logging.FileHandler("log.log")
        file_handler.setFormatter(formatter)  # 可以通过setFormatter指定输出格式
        # 控制台日志
        console_handler = logging.StreamHandler(sys.stdout)
        console_handler.formatter = formatter  # 也可以直接给formatter赋值
        # 为logger添加的日志处理器
        logger.addHandler(file_handler)
        logger.addHandler(console_handler)
        # 指定日志的最低输出级别，默认为WARN级别
        logger.setLevel(logging.WARNING)
        return logger

    def add_xls(self, xls_path, associated_mode="P"):
        """
            DESC：将xls文件及步骤-预计结果对应关系加入到待格式化列表self.xls_list
            PARAM：xls_path {string}  文件的绝对路径
                   associated_mode {char} 步骤-预计结果对应关系,默认为‘P’
            RETURN：None
        """
        xls_path = xls_path.replace("\\", "/")
        self.xls_list.append([xls_path, associated_mode])

    def add_dir_xls(self, dir_name=None):
        """
            DESC：查询指定目录下xls文件，并将文件的绝对路径以及步骤-预计结果对应关系加入到待格式化列表self.xls_list
            PARAM：dir_name {string} 默认值为None，此时取系统输入目录
            RETURN：NONE
        """
        if dir_name is None:
            dir_name = self.input_dir
        try:
            xls_files = [x for x in os.listdir(dir_name) if
                         os.path.isfile(os.path.join(self.input_dir, x)) and os.path.splitext(x)[1] == '.xls' or os.path.splitext(x)[1] == '.xlsx' or
                         os.path.splitext(x)[1] == '.XLS' or os.path.splitext(x)[1] == '.XLSX']
            for xls in xls_files:
                xls_path = os.path.join(self.input_dir, xls)
                self.add_xls(xls_path, self.ASSOCIATED_MODE)
        except Exception:
            exception_info = traceback.format_exc()
            print(exception_info)
            self.logger.error(exception_info)


    def init_param(self):
        """
            DESC:读取属性文件，并将属性值设置到CaseFormatter对象的对应字段
            PARAM：NONE
            RETURN：NONE
        """
        ###  读取配置文件性息 将所有信息加载进字典 ###
        with open('property.txt', 'r', encoding='utf-8') as propertyLines:
            for line in propertyLines:
                line = line.strip()
                if line == "" or line.find("=") == -1 or line.strip().startswith("#"):
                    continue
                line = line.split("=")
                key = line[0].strip()
                value = line[1].strip()
                print(key + ":" + value)
                self.property_dict[key] = value
        ###  读取UI检查关键字    ###
        ui_keyword_str = self.property_dict.get("UI检查")
        if ui_keyword_str is not None and ui_keyword_str != "":
            value_list = ui_keyword_str.split("|")
            check_ui_list = []
            for value in value_list:
                value = value.strip()
                check_ui_list.append(value)
            self.ui_keyword = check_ui_list
        ###  读取业务及数据文件检查关键字  ###
        logic_db_keyword_str = self.property_dict.get("业务及数据文件检查")
        if logic_db_keyword_str is not None and logic_db_keyword_str != "":
            value_list = logic_db_keyword_str.split("|")
            check_logic_list = []
            for value in value_list:
                value = value.strip()
                check_logic_list.append(value)
            self.logic_db_keyword = check_logic_list
        ###  读取业务及数据文件检查关键字  ###
        accounting_keyword_str = self.property_dict.get("账务分录")
        if accounting_keyword_str is not None and accounting_keyword_str != "":
            value_list = accounting_keyword_str.split("|")
            check_accounting_list = []
            for value in value_list:
                value = value.strip()
                check_accounting_list.append(value)
            self.accounting_keyword = check_accounting_list
        ###  转换列设置   ###
        trans_column = self.property_dict.get("转换列")
        if trans_column is not None and trans_column != "":
            value_list = trans_column.split("|")
            trans_code = 0
            for value in value_list:
                value = value.strip()
                if value == "D" and trans_code & 1 != 1:
                    trans_code |= 1
                elif value == "C" and trans_code & 2 != 2:
                    trans_code |= 2
                elif value == "S" and trans_code & 4 != 4:
                    trans_code |= 4
                elif value == "R" and trans_code & 8 != 8:
                    trans_code |= 8
            if trans_code != 0:
                self.TRANS_COLUMN = trans_code
            else:
                self.TRANS_COLUMN = 15
        ###     步骤预期结果   ###
        associate_code = self.property_dict.get("步骤-预期结果")
        if associate_code is not None and associate_code != "":
            if associate_code == "L":
                self.ASSOCIATED_MODE = "L"
            elif associate_code == "D":
                self.ASSOCIATED_MODE = "D"
            else:
                self.ASSOCIATED_MODE = "P"
        ###   Excel输入目录   ###
        input_dir = self.property_dict.get("Excel输入目录")
        if input_dir is not None and input_dir != "":
            input_dir = input_dir.strip()
            self.input_dir = input_dir
        ###   Excel输出目录   ###
        output_dir = self.property_dict.get("Excel输出目录")
        if output_dir is not None and output_dir != "":
            output_dir = output_dir.strip()
            self.output_dir = output_dir
        ###  步骤分隔设置  ###
        need_separator = self.property_dict.get("步骤分隔")
        separator = self.property_dict.get("步骤分隔符")
        if need_separator is not None and need_separator in ("N", "F"):
            self.case_upload == False
            self.separator = ""
        if self.case_upload is True and separator is not None:
            self.separator = separator

    def execute(self, out_dir_mode="U"):
        """
            DESC：格式化执行入口，对CaseFormatter对象xls_list里的每个xls文件进行格式化
            PARAM：NONE
            RETURN：NONE
        """
        try:
            if len(self.xls_list) == 0:
                print("No xls to format...Exit...")
                self.logger.error("No xls to format...Exit...")
            for xls_mode in self.xls_list:
                xls_name = xls_mode[0]
                associate_mode = xls_mode[1]
                print(xls_name)
                self.test_case_formatter(xls_name, associate_mode, out_dir_mode)
        except Exception:
            exception_info = traceback.format_exc()
            print(exception_info)
            self.logger.error(exception_info)

    #######  setters  and  getters  #######
    def set_input_dir(self, input_dir):
        self.input_dir = input_dir

    def set_output_dir(self, output_dir):
        self.output_dir = output_dir

    def set_sequence_rule(self, sequence_rule):
        self.sequence_rule = sequence_rule

    def set_logger(self, logger):
        self.logger = logger

    def set_accounting_keyword(self, accounting_keyword):
        self.accounting_keyword = accounting_keyword

    def set_ui_keyword(self, ui_keyword):
        self.ui_keyword = ui_keyword

    def set_logic_db_keyword(self, logic_db_keyword):
        self.logic_db_keyword = logic_db_keyword

    def add_accounting_keyword(self, accounting_keywords):
        self.accounting_keyword.extend(accounting_keywords)

    def add_ui_keyword(self, ui_keywords):
        self.ui_keyword.extend(ui_keywords)

    def add_logic_db_keyword(self, logic_db_keywords):
        self.logic_db_keyword.extend(logic_db_keywords)

    def add_sequence_rule(self, sequence_rules):
        self.sequence_rule.extend(sequence_rules)


if __name__ == "__main__":
    print("Start case formatter...")
    try:
        caseFormatter = BaseCaseFormatter()
        caseFormatter.init_param()
        caseFormatter.add_dir_xls()
        caseFormatter.execute()
        input("Press any to exit...")
    except:
        exception_info = traceback.format_exc()
        print(exception_info)